import pymysql
import sys
from prometheus_client import CollectorRegistry, Gauge, push_to_gateway
import requests

# create user monitoruser@'127.0.0.1' identified by '123456';
# grant replication client on *.* to monitoruser@'127.0.0.1';
# grant super on *.* to monitoruser@'127.0.0.1';

class MonitorItem(object):
    """
    所有监控项的基类
    """

    def __init__(self, user='monitoruser', password='123456', host='192.168.40.20', port=3306):
        """初始化属性与到数据库端的连接"""
        self.user = user
        self.password = password
        self.host = host
        self.port = port
        self.cnx = None
        self.cursor = None
        try:
            # config = {'user': self.user, 'password': self.password, 'host': self.host, 'port': self.port}
            self.cnx = pymysql.connect(
                host=self.host,  # 要连接的主机地址
                user=self.user,  # 用于登录的数据库用户
                password=self.password,  # 密码
                # database='testdb',      # 要连接的数据库
                port=self.port,  # 端口，一般为 3306
                charset='',  # 字符编码
                use_unicode=None,  # 是否使用 unicode 编码
                connect_timeout=10  # 连接超时时间，(default: 10, min: 1, max: 31536000)

            )
            # self.cursor = self.cnx.cursor(prepared=True)
            self.cursor = self.cnx.cursor()
        except pymysql.Error as err:
            """如果连接失败就赋空值"""
            self.cnx = None
            self.cursor = None
            sys.stderr.write(err.msg + '\n')

    def __str__(self):
        attrs = {}
        attrs['user'] = self.user
        attrs['password'] = self.password
        attrs['host'] = self.host
        attrs['port'] = self.port
        return "instance of {0}  {1}".format(self.__class__, attrs)

    def __del__(self):
        """在python 进行垃圾回收时关闭连接"""
        if self.cnx != None:
            self.cnx.close()

    def get_result(self):
        """返回监控项的状态,由子类实现相应的功能"""
        pass

    def print_result(self):
        """打印监控项的状态"""
        print(self.get_result())

    def action(self):
        """监控项达到阀值时可以触发的操作"""
        print("末定义任何有意义的操作")


########计算磁盘使用率##############
class MysqlDiskUsed(MonitorItem):
    def get_result(self):
        try:
            sql_cmd = "select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2)) as data_size,concat(truncate(sum(index_length)/1024/1024,2)) as index_size from information_schema.tables group by TABLE_SCHEMA order by data_length desc;"  # 单位MB
            self.cursor.execute(sql_cmd)
            row = self.cursor.fetchall()
            # self.cursor.close()
            # self.cnx.close()
            disk_size = 0
            for i in row:
                for j in i[1:]:
                    disk_size += float(j.decode('utf8'))
            disk_used_percent = disk_size / 50 * 100
            return int(disk_used_percent)
        except Exception as err:
            sys.stderr.write(err.__str__() + '\n')
            return -1


#####################################

################计算内存使用率##############
class MysqlMemUsed(MonitorItem):
    """计算内存使用率"""

    def get_result(self):
        try:
            sql_cmd = "select (@@key_buffer_size + @@query_cache_size + @@tmp_table_size +@@innodb_buffer_pool_size +@@innodb_additional_mem_pool_size +@@innodb_log_buffer_size +@@max_connections * (@@read_buffer_size +@@read_rnd_buffer_size +@@sort_buffer_size + @@join_buffer_size +@@binlog_cache_size +@@thread_stack)) /1024/1024/1024 AS MAX_MEMORY_GB;"
            self.cursor.execute(sql_cmd)
            row = self.cursor.fetchone()
            mem_used_GB = 0  # 代为GB
            mem_used_GB = float(row[0].decode('utf8'))
            mem_used_percent = mem_used_GB / 16 * 100
            return "%.2f" % mem_used_percent
        except Exception as err:
            sys.stderr.write(err.__str__() + '\n')
            return -1


# 以下类用于检测MySQL数据库的正常与否
class IsAlive(MonitorItem):
    """监控MySQL数据库是否正常运行、{正常:1,宕机:0}"""

    def get_result(self):
        if self.cnx != None:
            return "1"
        else:
            return "0"


# 以下类用于检测MySQL数据库的基本信息
class MysqlVariable(MonitorItem):
    """派生自MonitorItem类，用于所有variable 监控项的基类"""
    variable_name = None

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global variables like '{0}';""".format(self.variable_name)
                self.cursor.execute(stmt)
                return self.cursor.fetchone()[1].decode('utf8')
        except Exception as err:
            sys.stderr.write(err.__str__() + '\n')
            return -1


class MysqlPort(MonitorItem):
    """监控MySQL数据库监听是否正常、{正常:端口号,异常:-1}"""

    def get_result(self):
        if self.cnx != None:
            return self.port
        else:
            return -1


class MysqlBasedir(MysqlVariable):
    """监控MySQL安装目录所在位置，{正常:安装目录位置，异常:-1}"""
    variable_name = "basedir"


class MysqlDatadir(MysqlVariable):
    """监控MySQL数据目录所在位置，{正常:数据目录位置，异常:-1}"""
    variable_name = "datadir"


class MysqlVersion(MysqlVariable):
    """监控MySQL版本号，{正常:版本号，异常:-1}"""
    variable_name = "version"


class MysqlServerId(MysqlVariable):
    """监控MySQL的server_id"""
    variable_name = "server_id"


class MysqlLogBin(MysqlVariable):
    """binlog 是否有开启"""
    variable_name = "log_bin"


class MysqlLogError(MysqlVariable):
    """errorlog文件名"""
    variable_name = "log_error"


class MysqlPerformanceSchema(MysqlVariable):
    """performance_schema是否有开启"""
    variable_name = "performance_schema"


class MysqlInnodbBufferPoolSize(MysqlVariable):
    """监控MySQL innodb_buffer_pool的大小，{正常:缓冲池大小(byte)，异常:-1}"""
    variable_name = "innodb_buffer_pool_size"


class MysqlMaxConnections(MysqlVariable):
    """最大连接数"""
    variable_name = "max_connections"


# 派生自MonitorItem类，用于所有status 监控项的基类
class MysqlStatu(MonitorItem):
    """派生自MonitorItem类，用于所有statu 监控项的基类"""
    statu_name = None

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like '{0}';""".format(self.statu_name)
                self.cursor.execute(stmt)
                return self.cursor.fetchone()[1].decode('utf8')
        except Exception as err:
            sys.stderr.write(err.__str__() + '\n')
            return -1


class MysqlCurrentClient(MysqlStatu):
    """当前的客户端连接数"""
    statu_name = "Threads_connected"


class MysqlTableOpenCacheHitRate(MysqlStatu):
    """表缓存命中率"""

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like 'table_open_cache_hits';"""
                self.cursor.execute(stmt)
                hit = float((self.cursor.fetchone()[1].decode('utf8')))
                stmt = r"""show global status like 'table_open_cache_misses';"""
                self.cursor.execute(stmt)
                miss = float(self.cursor.fetchone()[1].decode('utf8'))
                return hit / (hit + miss)
        except Exception as err:
            sys.stderr.write(err.__str__())
            return -1


class MysqlTableOpenCacheOverflows(MysqlStatu):
    """表缓存溢出次数，如果大于0,可以增大table_open_cache和table_open_cache_instances."""
    statu_name = "Table_open_cache_overflows"


class MysqlTableLocksWaited(MysqlStatu):
    """因不能立刻获得表锁而等待的次数"""
    statu_name = "table_locks_waited"


class MysqlSlowqueries(MysqlStatu):
    """执行时间超过long_query_time的查询次数，不管慢查询日志有没有打开"""
    statu_name = "slow_queries"


class MysqlSortScan(MysqlStatu):
    """全表扫描之后又排序(排序键不是主键)的次数"""
    statu_name = "sort_scan"


class MysqlSortRows(MysqlStatu):
    """与sortscan差不多，前者指的是sortscan的次数，srotrows指的是sort操作影响的行数"""
    statu_name = "sort_rows"


class MysqlSortRange(MysqlStatu):
    """根据索引进行范围扫描之后再进行排序(排序键不能是主键)的次数"""
    statu_name = "sort_range"


class MysqlSortMergePasses(MysqlStatu):
    """排序时归并的次数，如果这个值比较大(要求高一点大于0)那么可以考虑增大sort_buffer_size的大小"""
    statu_name = "sort_merge_passes"


class MysqlSelectRangeCheck(MysqlStatu):
    """如果这个值不是0那么就要好好的检查表上的索引了"""
    statu_name = "select_range_check"


class MysqlQuestions(MysqlStatu):
    """erver端执行的语句数量，但是每执行一个语句它又只增加一，这点让我特别被动"""
    statu_name = "Questions"


class MysqlQcacheFreeMemory(MysqlStatu):
    """query cache 的可用内存大小"""
    statu_name = "qcache_free_memory"


class MysqlPreparedStmtCount(MysqlStatu):
    """由于本监控程序就是通过prepare语句完成的，所以这个监控项的值最少会是1不是0"""
    statu_name = "prepared_stmt_count"


class MysqlOpenedTables(MysqlStatu):
    """mysql数据库打开过的表，如果这个值过大，应该适当的增大table_open_cache的值"""
    statu_name = "opened_tables"


class MysqlOpenTables(MysqlStatu):
    """当前mysql数据库打开的表数量"""
    statu_name = "open_tables"


class MysqlServerLevelOpenFiles(MysqlStatu):
    """mysql数据库的server层当前正打开的文件数据"""
    statu_name = "open_files"


class MysqlInnodbAvailableUndoLogs(MysqlStatu):
    """innodb当前可用的undo段的数据"""
    statu_name = "innodb_available_undo_logs"


class MysqlInnodbNumOpenFiles(MysqlStatu):
    """innodb当前打开的文件数量"""
    statu_name = "innodb_num_open_files"


class MysqlInnodbRowsUpdated(MysqlStatu):
    """innodb层面执行的update所影响的行数"""
    statu_name = "innodb_rows_updated"


class MysqlInnodbRowsRead(MysqlStatu):
    """innodb 层面受读操作所影响的行数"""
    statu_name = "innodb_rows_read"


class MysqlInnodbRowsInserted(MysqlStatu):
    """innodb 层面受insert操作所影响的行数"""
    statu_name = "innodb_rows_inserted"


class MysqlInnodbRowsDeleted(MysqlStatu):
    """innodb 层面受delete操作所影响的行数"""
    statu_name = "innodb_rows_deleted"


class MysqlInnodbRowLockWaits(MysqlStatu):
    """innodb 行锁等待的次数"""
    statu_name = "innodb_row_lock_waits"


class MysqlInnodbRowLockTimeMax(MysqlStatu):
    """innodb层面行锁等待的最大毫秒数"""
    statu_name = "innodb_row_lock_time_max"


class MysqlInnodbRowLockTimeAvg(MysqlStatu):
    """innodb层面行锁等待的平均毫秒数"""
    statu_name = "Innodb_row_lock_time_avg"


class MysqlInnodbRowLockTime(MysqlStatu):
    """innodb层面行锁等待的总毫秒数"""
    statu_name = "Innodb_row_lock_time"


class MysqlInnodbPagesWritten(MysqlStatu):
    """innodb层面写入磁盘的页面数"""
    statu_name = "Innodb_pages_written"


class MysqlInnodbPagesRead(MysqlStatu):
    """从innodb buffer pool 中读取的页数"""
    statu_name = "Innodb_pages_read"


class MysqlInnodbOsLogWritten(MysqlStatu):
    """innodb redo 写入字节数"""
    statu_name = "Innodb_os_log_written"


class MysqlInnodbOsLogPendingWrites(MysqlStatu):
    """innodb redo log 被挂起的写操作次数"""
    statu_name = "Innodb_os_log_pending_writes"


class MysqlInnodbOsLogPendingFsyncs(MysqlStatu):
    """innodb redo log 被挂起的fsync操作次数"""
    statu_name = "Innodb_os_log_pending_fsyncs"


class MysqlInnodbOsLogFsyncs(MysqlStatu):
    """innodb redo log fsync的次数"""
    statu_name = "Innodb_os_log_fsyncs"


class MysqlInnodbLogWrites(MysqlStatu):
    """innodb redo log 物理写的次数"""
    statu_name = "innodb_log_writes"


class MysqlInnodbLogWriteRequests(MysqlStatu):
    """innodb redo log 逻辑写的次数"""
    statu_name = "Innodb_log_write_requests"


class MysqlInnodbLogWaits(MysqlStatu):
    """innodb 写redo 之前必须等待的次数"""
    statu_name = "Innodb_log_waits"


class MysqlInnodbDblwrWrites(MysqlStatu):
    """innodb double write 的次数"""
    statu_name = "Innodb_dblwr_writes"


class MysqlInnodbDblwrPagesWritten(MysqlStatu):
    """innodb double write 的页面数量"""
    statu_name = "Innodb_dblwr_pages_written"


class MysqlInnodbDoubleWriteLoader(MysqlStatu):
    """innodb double write 压力1~64、数值越大压力越大"""

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like 'innodb_dblwr_pages_written';"""
                self.cursor.execute(stmt)
                pages = float((self.cursor.fetchone()[1].decode('utf8')))
                stmt = r"""show global status like 'innodb_dblwr_writes';"""
                self.cursor.execute(stmt)
                requests = float(self.cursor.fetchone()[1].decode('utf8'))
                if requests == 0:
                    return 0
                return pages / requests
        except Exception as err:
            sys.stderr.write(err.__str__())
            return -1


class MysqlInnodbBufferPoolHitRate(MysqlStatu):
    """innodb buffer pool 命中率"""

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like 'innodb_buffer_pool_read_requests';"""
                self.cursor.execute(stmt)
                hit_read = float((self.cursor.fetchone()[1].decode('utf8')))
                stmt = r"""show global status like 'innodb_buffer_pool_reads';"""
                self.cursor.execute(stmt)
                miss_read = float(self.cursor.fetchone()[1].decode('utf8'))
                total_read = (miss_read + hit_read)
                if total_read == 0:
                    return 0
                return hit_read / total_read
        except Exception as err:
            sys.stderr.write(err.__str__())
            return -1


class MysqlInnodbBufferPoolFreePagePercent(MysqlStatu):
    """innodb buffer pool free page 百分比"""

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like 'innodb_buffer_pool_pages_total';"""
                self.cursor.execute(stmt)
                total_page = float((self.cursor.fetchone()[1].decode('utf8')))
                stmt = r"""show global status like 'innodb_buffer_pool_pages_free';"""
                self.cursor.execute(stmt)
                free_page = float(self.cursor.fetchone()[1].decode('utf8'))
                return free_page / total_page
        except Exception as err:
            sys.stderr.write(err.__str__())
            return -1


class MysqlInnodbBufferPoolDirtyPercent(MysqlStatu):
    """innodb buffer pool dirty page 百分比"""

    def get_result(self):
        try:
            if self.cursor != None:
                stmt = r"""show global status like 'innodb_buffer_pool_pages_total';"""
                self.cursor.execute(stmt)
                total_page = float((self.cursor.fetchone()[1].decode('utf8')))
                stmt = r"""show global status like 'innodb_buffer_pool_pages_dirty';"""
                self.cursor.execute(stmt)
                dirty_page = float(self.cursor.fetchone()[1].decode('utf8'))
                return dirty_page / total_page
        except Exception as err:
            sys.stderr.write(err.__str__())
            return -1


class MysqlCreated_tmp_disk_tables(MysqlStatu):
    """mysql运行时所创建的磁盘临时表的数量，如果这个数值比较大，可以适当的增大 tmp_table_size | max_heap_table_size"""
    statu_name = "Created_tmp_disk_tables"


class MysqlComSelect(MysqlStatu):
    """select 语句执行的次数"""
    statu_name = "com_select"


class MysqlComInsert(MysqlStatu):
    """insert 语句执行的次数"""
    statu_name = "com_insert"


class MysqlComDelete(MysqlStatu):
    """delete 语句执行的次数"""
    statu_name = "com_delete"


class MysqlComUpdate(MysqlStatu):
    """update 语句执行的次数"""
    statu_name = "com_update"


class MysqlBinlogCacheDiskUse(MysqlStatu):
    """事务引擎因binlog缓存不足而用到临时文件的次数，如果这个值过大，可以通过增大binlog_cache_size来解决"""
    statu_name = "Binlog_cache_disk_use"


class MysqlBinlogStmtCacheDiskUse(MysqlStatu):
    """非事务引擎因binlog缓存不足而用到临时文件的次数，如果这个值过大，可以通过增大binlog_stmt_cache_size来解决"""
    statu_name = "Binlog_stmt_cache_disk_use"


class MysqlReplication(MonitorItem):
    """所有监控mysql replication的基类"""

    def __init__(self, user='monitoruser', password='123456', host='127.0.0.1', port=3306):
        MonitorItem.__init__(self, user, password, host, port)
        try:
            if self.cursor != None:
                stmt = "show slave status;"
                self.cursor.execute(stmt)
                self.replication_info = self.cursor.fetchone()
        except Exception as err:
            pass


class MysqlReplicationIsRunning(MysqlReplication):
    """mysql replication 是否正常运行"""

    def get_result(self):
        if self.replication_info == None:
            return "replication is not running"
        else:
            slave_io_running = self.replication_info[10].decode('utf8')
            slave_sql_running = self.replication_info[11].decode('utf8')
            if slave_io_running == 'Yes' and slave_sql_running == 'Yes':
                return "running"
            return "replication is not running"


class MysqlReplicationBehindMaster(MysqlReplication):
    """监控seconde behind master """

    def get_result(self):
        if self.replication_info != None:
            return self.replication_info[32]
        else:
            return -1


# 监控项字典
items = {
    # 实例配置信息收集项
    'port': MysqlPort,
    #'baseDir': MysqlBasedir,
    #'dataDir': MysqlDatadir,
    #'version': MysqlVersion,
    #'serverId': MysqlServerId,
    #'isBinlogEnab': MysqlLogBin,
    #'isErrorlogEnab': MysqlLogError,
    #'isPerformanceScheamEnabe ': MysqlPerformanceSchema,
    'innodbBufferPoolSi': MysqlInnodbBufferPoolSize,
    'maxConnectio': MysqlMaxConnections,

    # 实例运行时信息收集项
    'isOnLine': IsAlive,
    'currentConnectio': MysqlCurrentClient,
    'tableCacheHitRa': MysqlTableOpenCacheHitRate,
    'tableOpenCacheOverflos': MysqlTableOpenCacheOverflows,
    'tableLocksWait': MysqlTableLocksWaited,
    'slowqueries': MysqlSlowqueries,
    'sortScan': MysqlSortScan,
    'sortRows': MysqlSortRows,
    'sortRange': MysqlSortRange,
    'sortMergePass': MysqlSortMergePasses,
    'selectRangeChe': MysqlSelectRangeCheck,
    'questions': MysqlQuestions,
    'qcacheFreeMemo': MysqlQcacheFreeMemory,
    'preparedStmtCou': MysqlPreparedStmtCount,
    'openedTables': MysqlOpenedTables,
    'openTables': MysqlOpenTables,
    'serverLevelOpenFil': MysqlServerLevelOpenFiles,
    'created_tmp_disk_tabls': MysqlCreated_tmp_disk_tables,
    'comSelect': MysqlComSelect,
    'comInsert': MysqlComInsert,
    'comDelete': MysqlComDelete,
    'comUpdate': MysqlComUpdate,
    'binlogCacheDiskU': MysqlBinlogCacheDiskUse,
    'binlogStmtCacheDiskUe': MysqlBinlogStmtCacheDiskUse,
    #'MysqlDiskUsd': MysqlDiskUsed,
    #'MysqlMemUsed': MysqlMemUsed,

    # innodb运行时信息收集项
    #'innodbAvailableUndoLos': MysqlInnodbAvailableUndoLogs,
    'innodbOpenFil': MysqlInnodbNumOpenFiles,
    'innodbRowsUpdat': MysqlInnodbRowsUpdated,
    'innodbRowsRe': MysqlInnodbRowsRead,
    'innodbRowsInsert': MysqlInnodbRowsInserted,
    'innodbRowsDelet': MysqlInnodbRowsDeleted,
    'innodbRowLockWai': MysqlInnodbRowLockWaits,
    'innodbRowLockTimeM': MysqlInnodbRowLockTimeMax,
    'innodbRowLockTimeA': MysqlInnodbRowLockTimeAvg,
    'innodbRowLockTi': MysqlInnodbRowLockTime,
    #'innodbPagesWritt': MysqlInnodbPagesWritten,
    #'innodbPagesRe': MysqlInnodbPagesRead,
    #'innodbOsLogWritt': MysqlInnodbOsLogWritten,
    #'innodbOsLogPendingWrits': MysqlInnodbOsLogPendingWrites,
    #'innodbOsLogPendingFsyns': MysqlInnodbOsLogPendingFsyncs,
    #'innodbOsLogFsyn': MysqlInnodbOsLogFsyncs,
    #'innodbLogWrit': MysqlInnodbLogWrites,
    #'innodbLogWriteRequess': MysqlInnodbLogWriteRequests,
    #'innodbLogWai': MysqlInnodbLogWaits,
    #'innodbDblwrWrit': MysqlInnodbDblwrWrites,
    #'innodbDblwrPagesWrittn': MysqlInnodbDblwrPagesWritten,
    #'innodbDoubleWriteLoadr': MysqlInnodbDoubleWriteLoader,
    #'innodbBufferPoolHitRae': MysqlInnodbBufferPoolHitRate,
    #'innodbBufferPoolFreePagePercet': MysqlInnodbBufferPoolFreePagePercent,
    #'innodbBufferPoolDirtyPercet': MysqlInnodbBufferPoolDirtyPercent,

    # 对mysql replication 的监控
    #'replicationIsRunni': MysqlReplicationIsRunning,
    #'replicationBehindMastr': MysqlReplicationBehindMaster,
}


if __name__ == "__main__":
    registry = CollectorRegistry()
    hostIP = '192.168.40.20'
    for myKey in items.keys():
        g = Gauge(myKey, '', ['process', 'instance'],
                  registry=registry)  # Guage(metric_name,HELP,labels_name,registry=registry)
        g.labels(myKey, '192.168.40.10').set(items[myKey].get_result(items[myKey]()))
        print(items[myKey].get_result(items[myKey]()))
        push_to_gateway('192.168.40.10:9091', job='mysql_status', registry=registry)




